﻿CREATE PROCEDURE [dbo].[kp_TraCuuHoSo]
	@Tu_Khoa nvarchar(255),
	@Ma_Px nvarchar(15),
	@Ma_LoaiHs nvarchar(15),
	@Ma_Tt int,
	@TuNgay datetime,
	@DenNgay datetime,
	@Ht int
AS
BEGIN
	Declare @sql nvarchar(max)
	set @sql='Select * from vDangKyKd WHERE 1=1 '
	IF @Tu_Khoa NOT LIKE '%[^0-9]%' AND LEN(ISNULL(@Tu_Khoa,''))>0
		BEGIN
			set @sql=@sql+N'AND ('
			set @sql=@sql+N'Ma_Hs='+CAST(@Tu_Khoa as nvarchar(20))					
			set @sql=@sql+N'  OR So_Gp='+CAST(@Tu_Khoa as nvarchar(20))
			set @sql=@sql+N'  OR So_Bn'+CAST(@Tu_Khoa as nvarchar(20))
			set @sql=@sql+N'  OR Ten_Hokd like N''%'+CAST(@Tu_Khoa as nvarchar(20))+'%'''
			set @sql=@sql+N'  OR Dia_Diem like N''%'+CAST(@Tu_Khoa as nvarchar(20))+'%'''
			set @sql=@sql+N'  OR Ho_Ten like N''%'+CAST(@Tu_Khoa as nvarchar(20))+'%'''
			set @sql=@sql+'		)'																			
		END
	ELSE
		BEGIN
			IF @Tu_Khoa IS NOT NULL
			BEGIN
				set @sql=@sql+N' AND ('
				set @sql=@sql+N'  OR Ten_Hokd like N''%'+cast(@Tu_Khoa as nvarchar(20))+'%'''
				set @sql=@sql+N'  OR Dia_Diem like N''%'+cast(@Tu_Khoa as nvarchar(20))+'%'''
				set @sql=@sql+N'  OR Ho_Ten like N''%'+cast(@Tu_Khoa as nvarchar(20))+'%'''
				set @sql=@sql+'		)'
			END
	END						
	
	IF @Ma_Px <> '000000' 
		set @sql = @sql +N' AND Ma_Px=''' + @Ma_Px + ''''
	
	IF @Ma_LoaiHs <> '000000'
		set @sql = @sql +N' AND Ma_LoaiHs=''' + @Ma_Px + ''''
	
	IF @Ma_Tt > 0
		Set @sql = @sql +N' AND Ma_Tt=' + CAST(@Ma_Tt as nvarchar(3))
		
	Declare @FromDate nvarchar(10),@ToData nvarchar(10)
	IF @TuNgay IS NOT NULL
	BEGIN
		set @FromDate= cast( FLOOR(CAST(@TuNgay as FLOAT)) as nvarchar(10))
		Set @sql = @sql +N' AND FLOOR(CAST(Ngay_Nop as FLOAT))>=' +@FromDate		
	END 
	
	IF @DenNgay IS NOT NULL
	BEGIN
		set @FromDate= cast( FLOOR(CAST(@DenNgay as FLOAT)) as nvarchar(10))
		Set @sql = @sql +N' AND FLOOR(CAST(Ngay_Nop as FLOAT))<=' +@FromDate		
	END 
	
	IF @Ht <> -1
		Set @sql = @sql +N' AND Ht='+ CAST(@Ht as nvarchar(2))
	
	RETURN 0;
END